﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;

namespace www.serviciipeweb.ro.CDC
{
    public static class Databasehelper
    {
        public static Table FromID(this Database obj, int ID)
        {
            return obj.Tables.Cast<Table>().SingleOrDefault<Table>
                (
                (t) => { return t.ID == ID; }
                );
        }
        //TODO : ask MS why _CT tables are not system objects....
        public static List<Table> NonSystemTables(this Database d)
        {
            List<Table> val = new List<Table>();
            foreach (Table t in d.Tables)
            {
                if (!d.IsSystemObject)
                    val.Add(t);
            }
            return val;
        }
        public static List<Table> NonSystemOrSchema(this Database d, string Schema)
        {
            List<Table> val = d.NonSystemTables();
            List<Table> ret =  new List<Table>();
            val.ForEach((t) =>
            {
                if (t.Schema != Schema)
                    ret.Add(t);
            }
            );
            return ret;
        }
        public static bool IsCDCEnabled(this Database d)
        {
            eServerCDC o;

            if (!d.Parent.IsCDCEnabled(out o))
                return false;
            //    if (<db>.[sys].[fn_cdc_is_db_enabled]() != 1)  
            using (SqlConnection scon = new SqlConnection())
            {
                scon.ConnectionString = SQLHelperLSN.SqlConnection;
                scon.Open();
                using (SqlCommand sc = new SqlCommand())
                {
                    sc.CommandType = System.Data.CommandType.Text;
                    sc.CommandText = (@"
                select name,is_cdc_enabled from sys.databases
                where [name] = " + SqlUtilities<string>.FormatSQL(d.Name));
                    sc.Connection = scon;
                    using (SqlDataReader sr = sc.ExecuteReader())
                    {
                        while (sr.Read())
                        {
                            return (bool.Parse(sr["is_cdc_enabled"].ToString()));
                        }
                        return false;
                    }
                }
            }
        }
        
        
        public static void ModifyCDC(this Database d, bool Modif)
        {
            if (d.IsCDCEnabled() == Modif)
                return;

            string sql = "use " +d.Name ;
            sql += ";";
            sql = "";
            if (Modif)
            {
             //CTP:  
             //sql += "exec " + d.Name + ".sys.sp_cdc_enable_db_change_data_capture;";
                sql += "exec " + d.Name + ".sys.sp_cdc_enable_db ";

            }
            else
            {
                //CTP
                //sql += "exec " + d.Name + ".sys.sp_cdc_disable_db_change_data_capture";
                sql += "exec " + d.Name + ".sys.sp_cdc_disable_db ";
            }
            using (SqlConnection scon = new SqlConnection())
            {
                scon.ConnectionString = SQLHelperLSN.SqlConnection;
                scon.Open();
                using (SqlCommand sc = new SqlCommand())
                {
                    sc.CommandType = System.Data.CommandType.Text;
                    sc.CommandText = sql;
                    sc.Connection = scon;
                    sc.ExecuteNonQuery();
                }

                
            }
            

        }
    }
}
